const {as4KDotsHolderBefore10} = require("../db/init");
const {Op, sequelize} = require("../db");
const { QueryTypes } = require('sequelize');

/**
 * 不包含退市\风险股
 * @param offset
 * @param limit
 * @param count
 * @returns {Promise<*[]>}
 */
module.exports.listJJ = async function (offset, limit) {
    return await as4KDotsHolderBefore10.findAndCountAll({
        attributes: [
            'id',
            'stock_name',
            'stock_code',
            'holder_name',
            'hold_num',
            'hold_change'
        ],
        where: {
            [Op.and]: [sequelize.where(sequelize.fn('char_length', sequelize.col('holder_name')), {
                [Op.gt]: 3
            }), {
                stock_name: {
                    [Op.notLike]: '%退%'
                }
            }, {
                stock_name: {
                    [Op.notLike]: '%st%'
                }
            }]
        },
        offset: parseInt(offset),
        limit: parseInt(limit),
        // group: 'stock_code'

    })
}
/**
 * 列出指定机构的持仓
 * @param offset
 * @param limit
 * @param jj_name
 * @returns {Promise<*>}
 */
module.exports.findAllStockHoldByHolderName = async function (offset, limit, jj_name) {
    let baseInfo = await as4KDotsHolderBefore10.findAndCountAll({
        order:[
            ['hold_num','desc']
        ],
        // attributes: [
        //     'id',
        //     'stock_name',
        //     'stock_code',
        //     'holder_name',
        //     'hold_num',
        //     'hold_change'
        // ],
         where: {
            holder_name: jj_name
        },
        offset: parseInt(offset),
        limit: parseInt(limit),
    });

    return baseInfo
}
/**
 * 机构持仓总数
 * @param jj_name 机构的名字,全名字
 * @returns {Promise<*>}
 */
module.exports.countAllStockHoldByHolderName = async function (jj_name) {
    let baseInfo = await as4KDotsHolderBefore10.count({
        where: {
            holder_name: jj_name
        }
    });

    return baseInfo
}

/**
 * 列出股东包含一定数量的机构持仓股
 * @param offset
 * @param limit
 * @returns {Promise<*>}
 */
module.exports.findJJHoldCount = async function (offset, limit) {
    let sql = `SELECT count(*) AS num, holder_name
FROM as4k_dots_holder_before10
WHERE char_length(holder_name) > 3
GROUP BY holder_name
HAVING num > 3
order by num desc limit ${offset},${limit}`
    let baseInfo = await sequelize.query(sql,{ type: QueryTypes.SELECT })
    return baseInfo
}
